跳到主要内容

MySQL 的索引下推是啥

什么是索引下推​

索引下推（Index Condition Pushdown）是一种优化技术，用于在查询执行过程中将过滤条件尽可能地下推到存储引擎层级执行，减少从存储引擎返回的数据量，提高查询性能。

在传统的查询执行流程中，当执行一个带有过滤条件的查询时，数据库会首先从存储引擎层级获取满足索引条件的行，然后再在数据库层级执行过滤条件，过滤掉不符合条件的行，最后返回满足条件的结果集。

而索引下推则通过将过滤条件下推到存储引擎层级，提前进行过滤操作，减少从存储引擎返回的数据量。这样可以减少数据库层级的处理和传输开销，从而提高查询性能。

索引下推的工作原理如下：

  1. 查询优化器在执行计划生成阶段，将查询条件分为可下推和不可下推的条件。可下推的条件是指那些可以在存储引擎层级进行过滤的条件，例如等值匹配或范围匹配的条件。

  2. 查询优化器在生成执行计划时，会将可下推的条件下推到存储引擎层级，并在存储引擎执行阶段进行过滤操作。

  3. 存储引擎在接收到下推的条件后，利用索引信息进行过滤操作，只返回满足条件的数据行给数据库层级。

通过索引下推，可以减少数据库层级的数据处理和传输开销，提高查询性能。特别是在涉及大量数据和复杂查询条件的情况下，索引下推可以显著减少不必要的数据传输和处理，提升查询效率。

举例说明​

使用一张用户表 tuser，表里创建联合索引（name, age）。

如果现在有一个需求：检索出表中 “名字第一个字是张，而且年龄是10岁的所有用户”。那么，SQL语句是这么写的：

select * from tuser where name like '张%' and age=10;

假如你了解索引最左匹配原则，那么就知道这个语句在搜索索引树的时候，只能用 "张"，找到的第一个满足条件的记录id为1。

那接下来的步骤是什么呢？

没有使用 ICP​

在 MySQL 5.6 之前，存储引擎根据通过联合索引找到 name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server 层再对数据根据 age = 10 进行筛选。

可以看到需要回表两次，把我们联合索引的另一个字段 age 浪费了。

使用ICP​

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到 name like '张%'，由于联合索引中包含 age 列，所以存储引擎直接再联合索引里按照 age=10 过滤。按照过滤后的数据再一一进行回表扫描。

我们看一下示意图：

可以看到只回表了一次。

除此之外我们还可以看一下执行计划，看到 Extra 一列里 Using index condition，这就是用到了索引下推。

+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | tuser | NULL       | range | na_index      | na_index | 102     | NULL |    2 |    25.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+

索引下推使用条件​

  • 只能用于 range、 ref、 eq_ref、ref_or_null 访问方法；
  • 只能用于 InnoDB 和 MyISAM 存储引擎及其分区表；
  • 对 InnoDB 存储引擎来说，索引下推只适用于二级索引（也叫辅助索引）;
  • 引用了子查询的条件不能下推；
  • 引用了存储函数的条件不能下推，因为存储引擎无法调用存储函数。

索引下推的目的是为了减少回表次数，也就是要减少IO操作。对于InnoDB的聚簇索引来说，数据和索引是在一起的，不存在回表这一说。

相关系统参数​

索引条件下推默认是开启的，可以使用系统参数 optimizer_switch 来控制器是否开启。

查看默认状态：

mysql> select @@optimizer_switch\G;
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on
1 row in set (0.00 sec)

切换状态：

set optimizer_switch="index_condition_pushdown=off";
set optimizer_switch="index_condition_pushdown=on";

References​